package spring.data.jpa.repository;

import java.util.Collections;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.persistence.Entity;
import javax.persistence.EntityManager;
import javax.persistence.Query;

import org.apache.commons.lang3.StringUtils;
import org.hibernate.query.NativeQuery;
import org.hibernate.transform.Transformers;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.query.QueryUtils;
import org.springframework.data.repository.NoRepositoryBean;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.Assert;

import spring.data.jpa.transform.BeanResultTransformer;

/**
 * 注入entityManager就可以直接作为一个repository<br>
 * 时间： 2018年4月20日 下午2:50:54
 * @author wangjie
 *
 */
@SuppressWarnings({ "unchecked", "deprecation" })
@NoRepositoryBean
public interface SQLExecutor {

    public static final String COUNT_PREFIX = StringUtils.substring(QueryUtils.COUNT_QUERY_STRING, 0,
            QueryUtils.COUNT_QUERY_STRING.length() - 1);
    /** select count(1) from */
    public static final String COUNT_SQL = String.format(COUNT_PREFIX, "1", StringUtils.EMPTY);

    /**
     * 按sql查询所有对象,参数从 0 开始依次绑定
     * @param sql sql语句
     * @param clazz 查询类型
     * @param values 过滤条件
     * @return 符合查询语句的所有对象集合
     */
    @Transactional(readOnly = true)
    default <X> List<X> findBySql(String sql, Class<X> clazz, Object... values) {
        Entity entity = clazz.getAnnotation(Entity.class);
        Query query;
        if (entity == null) {
            query = getNativeQuery(sql, values);
            query.unwrap(NativeQuery.class).setResultTransformer(new BeanResultTransformer(clazz));
        } else {
            query = getNativeQuery(sql, clazz, values);
        }
        return query.getResultList();
    }

    /**
     * 按sql查询所有对象,参数从 0 开始依次绑定
     * @param sql sql语句
     * @param values 过滤条件
     * @return 符合查询语句的所有对象集合
     */
    @Transactional(readOnly = true)
    default List<?> findBySql(String sql, boolean toMap, Object... values) {
        Query query;
        if (toMap) {
            query = getNativeQuery(sql, values);
            query.unwrap(NativeQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        } else {
            query = getNativeQuery(sql, values);
        }
        return query.getResultList();
    }

    /**
     * 按sql查询所有对象,参数按名称绑定
     * @param sql sql语句
     * @param values 绑定的参数
     * @return 扶额和查询语句的所有对象集合
     */
    @Transactional(readOnly = true)
    default List<?> findBySql(String sql, boolean toMap, Map<String, Object> values) {
        Query query;
        if (toMap) {
            query = getNativeQuery(sql, values);
            query.unwrap(NativeQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        } else {
            query = getNativeQuery(sql, values);
        }
        return query.getResultList();
    }

    /**
     * 按sql查询所有对象,参数按名称绑定
     * @param sql sql语句
     * @param clazz 查询类型
     * @param values 绑定的参数
     * @return 扶额和查询语句的所有对象集合
     */
    @Transactional(readOnly = true)
    default <X> List<X> findBySql(String sql, Class<X> clazz, Map<String, Object> values) {
        Entity entity = clazz.getAnnotation(Entity.class);
        Query query;
        if (entity == null) {
            query = getNativeQuery(sql, values);
            query.unwrap(NativeQuery.class).setResultTransformer(new BeanResultTransformer(clazz));
        } else {
            query = getNativeQuery(sql, clazz, values);
        }
        return query.getResultList();
    }

    /**
     * 根据sql查询对象的分页,注意,此处的pageable不指定Sort对象,要排序直接在sql语句中写
     * @param sql sql语句
     * @param pageable 分页对象
     * @param values sql参数,从 0 开始绑定
     * @return 符合查询的分页对象
     */
    @Transactional(readOnly = true)
    default <X> Page<X> findBySql(String sql, Class<X> clazz, Pageable pageable, Object... values) {
        Query query;
        Entity entity = clazz.getAnnotation(Entity.class);
        if (entity == null) {
            query = getNativeQuery(sql, values);
            query.unwrap(NativeQuery.class).setResultTransformer(new BeanResultTransformer(clazz));
        } else {
            query = getNativeQuery(sql, clazz, values);
        }
        query.setFirstResult((int) pageable.getOffset());
        query.setMaxResults(pageable.getPageSize());

        Long total = ((Number) getCountNativeQuery(sql, values).getSingleResult()).longValue();
        List<X> content = total > pageable.getOffset() ? query.getResultList() : Collections.emptyList();
        return new PageImpl<X>(content, pageable, total);
    }

    /**
     * 根据sql查询对象的分页,注意,此处的pageable不指定Sort对象,要排序直接在sql语句中写
     * @param sql sql语句 
     * @param pageable 分页对象
     * @param values sql参数,按名称绑定
     * @return 符合查询的分页对象
     */
    @Transactional(readOnly = true)
    default <X> Page<X> findBySql(String sql, Class<X> clazz, Pageable pageable, Map<String, Object> values) {
        Query query;
        Entity entity = clazz.getAnnotation(Entity.class);
        if (entity == null) {
            query = getNativeQuery(sql, values);
            query.unwrap(NativeQuery.class).setResultTransformer(new BeanResultTransformer(clazz));
        } else {
            query = getNativeQuery(sql, clazz, values);
        }
        query.setFirstResult((int) pageable.getOffset());
        query.setMaxResults(pageable.getPageSize());

        Long total = ((Number) getCountNativeQuery(sql, values).getSingleResult()).longValue();
        List<X> content = total > pageable.getOffset() ? query.getResultList() : Collections.emptyList();
        return new PageImpl<X>(content, pageable, total);
    }

    /**
     * 执行更新或删除语句
     * @param sql sql语句
     * @param values 参数,从0 开始依次绑定
     * @return
     */
    @Transactional
    default int executeSql(String sql, Object... values) {
        return getNativeQuery(sql, values).executeUpdate();
    }

    /**
     * 执行更新或删除语句
     * @param sql sql语句
     * @param values 参数,按名称绑定
     * @return
     */
    @Transactional
    default int executeSql(String sql, Map<String, Object> values) {
        return getNativeQuery(sql, values).executeUpdate();
    }

    default String getCountNativeSql(String sql) {
        Pattern pattern = Pattern.compile("\\(.*\\)");
        Matcher matcher = pattern.matcher(sql);
        while (matcher.find()) {
            sql = StringUtils.substring(sql, matcher.start(), matcher.end()).trim();
            sql = StringUtils.substring(sql, 1, sql.length() - 1);
            matcher = pattern.matcher(sql);
        }
        return sql.toLowerCase();
    }

    default Query getCountNativeQuery(String sql, Object... values) {
        String countSql = COUNT_SQL + StringUtils.substringAfter(getCountNativeSql(sql), "from");
        return getNativeQuery(countSql, values);
    }

    default Query getCountNativeQuery(String sql, Map<String, Object> values) {
        String countSql = COUNT_SQL + StringUtils.substringAfter(getCountNativeSql(sql), "from");
        return getNativeQuery(countSql, values);
    }

    default Query getNativeQuery(String sql, Object... values) {
        Assert.hasText(sql, "sql不能为空.");
        Query query = this.getEntityManager().createNativeQuery(sql);
        if (values != null) {
            for (int i = 0; i < values.length; i++) {
                query.setParameter(i + 1, values[i]);
            }
        }
        return query;
    }

    default <X> Query getNativeQuery(String sql, Class<X> clazz, Object... values) {
        Assert.hasText(sql, "sql不能为空.");
        Query query = this.getEntityManager().createNativeQuery(sql, clazz);
        if (values != null) {
            for (int i = 0; i < values.length; i++) {
                query.setParameter(i + 1, values[i]);
            }
        }
        return query;
    }

    default Query getNativeQuery(String sql, Map<String, Object> values) {
        Assert.hasText(sql, "sql不能为空.");
        Query query = this.getEntityManager().createNativeQuery(sql);
        if (values != null) {
            for (Entry<String, Object> entry : values.entrySet()) {
                query.setParameter(entry.getKey(), entry.getValue());
            }
        }
        return query;
    }

    default <X> Query getNativeQuery(String sql, Class<X> clazz, Map<String, Object> values) {
        Assert.hasText(sql, "sql不能为空.");
        Query query = this.getEntityManager().createNativeQuery(sql, clazz);
        if (values != null) {
            for (Entry<String, Object> entry : values.entrySet()) {
                query.setParameter(entry.getKey(), entry.getValue());
            }
        }
        return query;
    }

    public EntityManager getEntityManager();
}
